package util;

import atmGUI.User;
import jdbc.Student;

import java.sql.*;


public class JDBCUtil {
    /**
     * 事务
     */
    public boolean testTransition(User user, User user1) {
        Connection con = getConnection();
        PreparedStatement statement = null;
        String sql = "update t_user set u_balance=? where u_id=?";
        try {
            //  con.setAutoCommit(false);
            statement = con.prepareStatement(sql);
            statement.setObject(1, user.getBalance());
            statement.setObject(2, user.getId());
            statement.executeUpdate();
            int a = 100 / 0;
            statement = con.prepareStatement(sql);
            statement.setObject(1, user1.getBalance());
            statement.setObject(2, user1.getId());
            statement.executeUpdate();
            //    con.commit();
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ArithmeticException e) {

        } finally {
            close(con, statement);
        }
        return false;
    }


    /**
     * 加载驱动
     *
     * @return
     */
    public Connection getConnection() {
// 1. 加载驱动
        Connection con = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 创建连接
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/atm?userSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT",
                    "root", "926445");
            System.out.println("con:" + con);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    /**
     * 根据用户名和密码查询用户对象
     *
     * @param userAccount
     * @param password
     * @return
     */
    public User getByUserAccountAndPassword(String userAccount, String password) {
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        User user = null;
        String sql = "select * from t_user where u_account=? and u_password=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, userAccount);
            statement.setObject(2, password);
            rs = statement.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("u_id"));
                user.setAccount(rs.getString("u_account"));
                user.setPassword(rs.getString("u_password"));
                user.setBalance(rs.getDouble("u_balance"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return user;
    }

    /**
     * 根据id查询
     *
     * @param id
     * @return
     */
    public User getById(int id) {
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        User user = null;
        String sql = "select * from t_user where u_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            rs = statement.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("u_id"));
                user.setAccount(rs.getString("u_account"));
                user.setPassword(rs.getString("u_password"));
                user.setBalance(rs.getDouble("u_balance"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return user;
    }

    /**
     * 根据用户名查询用户对象
     *
     * @param account
     * @return
     */
    public User getByUserAccount(String account) {
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        User user = null;
        String sql = "select * from t_user where u_account=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, account);
            rs = statement.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("u_id"));
                user.setAccount(rs.getString("u_account"));
                user.setPassword(rs.getString("u_password"));
                user.setBalance(rs.getDouble("u_balance"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return user;
    }

    /**
     * 根据User对象中的数据进行修改
     *
     * @param user
     */
    public void update(User user) {
        // 1. 创建连接
        Connection con = getConnection();
        PreparedStatement statement = null;
        String sql = "update t_user set u_password=?,u_balance=? where u_id=?";
        try {
            // 2. 获取执行SQL语句对象
            statement = con.prepareStatement(sql);
            statement.setObject(1, user.getPassword());
            statement.setObject(2, user.getBalance());
            statement.setObject(3, user.getId());
            // 3. 执行SQL语句
            int row = statement.executeUpdate();
            System.out.println("受影响的行数：" + row);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 4. 关闭连接
            close(con, statement);
        }
    }

    /**
     * 封装close
     *
     * @param con
     * @param statement
     */
    public void close(Connection con, Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 重载查询close
     *
     * @param statement
     * @param con
     */
    public void close(Connection con, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

//    /**
//     * 增加数据
//     */
//    public  void insert(String sql) {
//        //1.创建连接
//        Connection con = getConnection();
//        Statement statement = null;
//        try {
//            //2.获取执行SQL语句对象
//            statement = con.createStatement();
//            // String sql = "insert into t_user(u_account,u_password,u_balance) values ('" + account + "','" + password + "'," + balance + ")";
//            //3.执行SQL语句
//            int row = statement.executeUpdate(sql);
//            System.out.println("受影响的行数：" + row);
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        } finally {
//            //4.关闭连接
//            close(statement, con);
//        }
//
//    }
//
//    /**
//     * 查询
//     * 和添加的区别在于查询会返回一个结果集，返回集需要关闭
//     */
//    public  ArrayList query() {
//        //1.创建连接
//        Connection con = getConnection();
//        Statement statement = null;
//        ResultSet rs = null;
//        List userList = new ArrayList();
//        try {
//            //2.获取执行SQL语句的对象
//            statement = con.createStatement();
//            String sql = "select u_id,u_account,u_password,u_balance from t_user";
//            //3.执行SQL并接收返回的一个结果集
//            rs = statement.executeQuery(sql);
//            //ResultSet rs 是一个的结果集
//            //rs.next()可以返回一个布尔值
//            while (rs.next()){
////            int id = rs.getInt(1);//根据上方sql字符串语句来决定get.什么类型来获取，参数为从下标几开始
////                String name = rs.getString(2);
////                int age = rs.getInt(3);
////                String gender = rs.getString ("s_gender");
////                System.out.println(id + "" + name + " " + age + " " + gender);
////               System.out.println(rs.getString( "name"));
//                //封装为集合
//                User user = new User();
//                user.setId(rs.getInt("u_id"));
//                user.setAccount(rs.getString("u_account"));
//                user.setPassword(rs.getString("u_password"));
//                user.setBalance(rs.getInt("u_balance"));
//                userList.add(user);
//            }
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }finally {
//           close(statement,con,rs);
//        }
//        return (ArrayList) userList;
//    }


}
